﻿/****************************** Module Header ******************************\
 * Module Name:  DbParams.cs
 * Module Version:  1.0.0 (2012-9-4)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to list the file type.
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;

namespace Yule8xCommon.Db
{
    /// <summary>
    /// 数据库参数对象
    /// <para>提供存储过程参数的相关方法</para>
    /// <para>DbParams.New(DBHelper.GetDbCommand(providerName)).Add&lt;int&gt;("intOutCode").AddRange&lt;string&gt;(new string[] { "@loginid", "@password", "@ip" }, loginid, password, ip).AddRange&lt;int&gt;(new string[] { "type", "code" }, 5, 6).ToArrayDbParameters()</para>
    /// </summary>
    /// <typeparam name="K"></typeparam>
    /// <typeparam name="V"></typeparam>
    public class DbParams
    {
        /// <summary>
        /// 参数对象列表
        /// </summary>
        private IList<DbParameter> lstParams;
        /// <summary>
        /// 通用数据库命令对象
        /// </summary>
        private DbCommand cmd;

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="providerName"></param>
        public DbParams(string providerName)
        {
            cmd = DbProviderFactories.GetFactory(providerName).CreateCommand();
            lstParams = new List<DbParameter>();
        }

        /// <summary>
        /// 重置实例对象，清空所有参数对象
        /// </summary>
        public void Clear()
        {
            this.lstParams.Clear();
        }

        /// <summary>
        /// 添加一个输入参数
        /// </summary>
        /// <typeparam name="T">参数类型</typeparam>
        /// <param name="parameterName">参数名称</param>
        /// <param name="value">值</param>
        /// <param name="size">值的长度</param>
        /// <param name="direct">参数方向</param>
        public void AddValue<T>(string parameterName, T value, int size, ParameterDirection direct)
        {
            this.lstParams.Add(this.NewInputParameter<T>(parameterName, value, size, direct));
        }

        /// <summary>
        /// 添加一个输入参数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <param name="size"></param>
        public void AddValue<T>(string parameterName, T value, int size)
        {
            this.AddValue<T>(parameterName, value, size, ParameterDirection.Input);
        }
        /// <summary>
        /// 添加一个输入参数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        public void AddValue<T>(string parameterName, T value)
        {
            this.AddValue<T>(parameterName, value, 0, ParameterDirection.Input);
        }
        public void AddInputString(string parameterName, string value, int size = 0)
        {
            this.lstParams.Add(this.NewInputParameter(parameterName, value, p =>
            {
                p.DbType = DbType.String;
                if (size > 0)
                {
                    p.Size = size;
                }
            }));
        }
        public void AddInputInteger(string parameterName, int value)
        {
            this.lstParams.Add(this.NewInputParameter(parameterName, value, p => { p.DbType = DbType.Int32; }));
        }
        public void AddInputDecimal(string parameterName, decimal value)
        {
            this.lstParams.Add(this.NewInputParameter(parameterName, value, p => { p.DbType = DbType.Decimal; }));
        }
        public void AddInputDouble(string parameterName, double value)
        {
            this.lstParams.Add(this.NewInputParameter(parameterName, value, p => { p.DbType = DbType.Double; }));
        }
        public void AddInputDateTime(string parameterName, DateTime value)
        {
            this.lstParams.Add(this.NewInputParameter(parameterName, value, p => { p.DbType = DbType.DateTime; }));
        }

        /// <summary>
        /// 添加一个输出参数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterNames"></param>
        public void AddOutput<T>(params string[] parameterNames)
        {
            foreach (string parameterName in parameterNames)
            {
                this.lstParams.Add(this.NewOutputParameter<T>(parameterName));
            }
        }
        /// <summary>
        /// 添加一个返回参数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterNames"></param>
        public void AddReturn(params string[] parameterNames)
        {
            foreach (string parameterName in parameterNames)
            {
                this.lstParams.Add(this.NewReturnParameter(parameterName));
            }
        }
        /// <summary>
        /// 批量添加输入参数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterNames"></param>
        /// <param name="values"></param>
        public void AddRange<T>(string[] parameterNames, params T[] values)
        {
            int i = 0;
            foreach (string paraName in parameterNames)
            {
                this.AddValue<T>(paraName, values[i]);
                i++;
            }
        }

        /// <summary>
        /// 获取一个参数对象实例，参数方向是输入的
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <param name="size"></param>
        /// <param name="direct"></param>
        /// <returns></returns>
        public DbParameter NewInputParameter<T>(string parameterName, T value, int size, ParameterDirection direct)
        {
            DbParameter para = this.cmd.CreateParameter();
            para.ParameterName = parameterName;
            Type type = value.GetType();
            para.DbType = ClrTypeToDbType(type);
            para.Value = value;
            if (size > 0) para.Size = size;
            para.Direction = direct;
            return para;
        }
        /// <summary>
        /// 获取一个参数对象实例，参数方向是输入的
        /// <para>方法已设置以下属性: ParameterName, Value, Direction</para>
        /// <para>action一般设置DbType, Size两个属性</para>
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        /// <remarks>2017-4-22</remarks>
        public DbParameter NewInputParameter(string parameterName, object value, Action<DbParameter> action)
        {
            DbParameter para = this.cmd.CreateParameter();
            para.ParameterName = parameterName;
            para.Value = value;
            action(para);
            para.Direction = ParameterDirection.Input;
            return para;
        }
        /// <summary>
        /// 获取一个参数对象实例，参数方向是输入的
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public DbParameter NewInputParameter<T>(string parameterName, T value, int size)
        {
            return this.NewInputParameter(parameterName, value, size, ParameterDirection.Input);
        }
        /// <summary>
        /// 获取一个参数对象实例，参数方向是输入的
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public DbParameter NewInputParameter<T>(string parameterName, T value)
        {
            return this.NewInputParameter(parameterName, value, 0);
        }
        /// <summary>
        /// 获取一个参数对象实例，参数方向是输出的
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public DbParameter NewOutputParameter<T>(string parameterName)
        {
            DbParameter para = this.cmd.CreateParameter();
            para.ParameterName = parameterName;
            Type type = typeof(T);
            para.DbType = ClrTypeToDbType(type);
            if (type == typeof(string) || type == typeof(DateTime)) para.Size = 255;
            para.Direction = ParameterDirection.Output;
            return para;
        }

        public DbParameter NewReturnParameter(string parameterName)
        {
            DbParameter para = this.cmd.CreateParameter();
            para.ParameterName = parameterName;
            para.DbType = DbType.Int32;
            para.Direction = ParameterDirection.ReturnValue;
            return para;
        }
        public DbParameter NewInputOutputParameter<T>(string parameterName)
        {
            DbParameter para = this.cmd.CreateParameter();
            para.ParameterName = parameterName;
            Type type = typeof(T);
            para.DbType = ClrTypeToDbType(type);
            if (type == typeof(string) || type == typeof(DateTime)) para.Size = 255;
            para.Direction = ParameterDirection.InputOutput;
            return para;
        }

        /// <summary>
        /// 获取DbParameter格式的数组
        /// </summary>
        /// <returns></returns>
        public DbParameter[] ToArray()
        {
            int intCount = this.lstParams.Count;
            DbParameter[] arrPara = new DbParameter[intCount];
            int i = 0;
            foreach (DbParameter para in this.lstParams)
            {
                arrPara[i] = para;
                i++;
            }
            return arrPara;
        }

        /// <summary>
        /// 获取DbParameter格式的数组
        /// </summary>
        /// <returns></returns>
        public DbParameter[] ToArrayDbParameters()
        {
            int intCount = this.lstParams.Count;
            DbParameter[] arrPara = new DbParameter[intCount];
            int i = 0;
            foreach (DbParameter para in this.lstParams)
            {
                arrPara[i] = para;
                i++;
            }
            return arrPara;
        }

        /// <summary>
        /// C#数据类型转换为托管环境数据库数据类型
        /// </summary>
        /// <param name="clrType"></param>
        /// <returns></returns>
        public static DbType ClrTypeToDbType(Type clrType)
        {
            if (DbTypeUtil.IsGType<int>(clrType)) return DbType.Int32;
            if (clrType == typeof(string) || DbTypeUtil.IsGType<DateTime>(clrType)) return DbType.String;
            if (DbTypeUtil.IsGType<double>(clrType)) return DbType.Double;
            if (DbTypeUtil.IsGType<bool>(clrType)) return DbType.Boolean;
            if (DbTypeUtil.IsGType<short>(clrType)) return DbType.Int16;
            if (DbTypeUtil.IsGType<Guid>(clrType)) return DbType.Guid;
            if (DbTypeUtil.IsGType<decimal>(clrType)) return DbType.Decimal;
            if (clrType == typeof(System.Xml.Linq.XElement)) return DbType.Xml;
            if (clrType == typeof(byte[])) return DbType.Binary;
            if (clrType == typeof(object)) return DbType.Object;
            if (DbTypeUtil.IsGType<long>(clrType)) return DbType.Int64;
            return DbType.String;
        }

        /// <summary>
        /// C#数据类型转为SQL数据类型名称
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static string ClrTypeToDbTypeName(Type type)
        {
            if (DbTypeUtil.IsGType<int>(type)) return "int";
            if (type == typeof(string)) return "nvarchar";
            if (DbTypeUtil.IsGType<short>(type)) return "smallint";
            if (DbTypeUtil.IsGType<DateTime>(type)) return "datetime";
            if (DbTypeUtil.IsGType<bool>(type)) return "bit";
            if (DbTypeUtil.IsGType<Guid>(type)) return "uniqueidentifier";
            if (type == typeof(byte[])) return "image";
            if (DbTypeUtil.IsGType<double>(type)) return "float";
            if (DbTypeUtil.IsGType<decimal>(type)) return "money";
            if (type == typeof(System.Xml.Linq.XElement)) return "xml";
            if (DbTypeUtil.IsGType<byte>(type)) return "tinyint";
            if (DbTypeUtil.IsGType<long>(type)) return "bigint";
            if (DbTypeUtil.IsGType<TimeSpan>(type)) return "time(7)";
            if (DbTypeUtil.IsGType<DateTimeOffset>(type)) return "datetimeoffset(7)";
            if (DbTypeUtil.IsGType<Single>(type)) return "real";
            return type.ToString().ToLower();
        }
    }
}
